﻿<#
    Script  : SqlAdminModule
    Author  : Riwut Libinuko
    Blog    : http://blog.libinuko.com 
    Copyright :© IdeasFree - cakriwut@gmail.com, 2011. All rights reserved.
#>

<# 
   Import-PSModule SqlAdminModule
#>
$references = @("Microsoft.SqlServer.SMO","Microsoft.SqlServer.SmoExtended","Microsoft.SqlServer.SqlEnum","Microsoft.SqlServer.SmoEnum")
$references |% {
   [System.Reflection.Assembly]::LoadWithPartialName($_) | Out-Null
}

function Show-Database
{


<#
.SYNOPSIS
Show-Database file on the server. 
		
.DESCRIPTION
Show-Database on the server
    
.INPUTS
None. You can not pipe objects to Shrink-DatabaseLog.
	
.OUTPUTS
Table of non-system database in form of @(Name, DBSize, LogSize, TotalSize, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable). 
		
.PARAMETER SQLServer
MANDATORY parameter to define target SQLServer 			



.EXAMPLE
	PS>  Audit-Database MySqlServer 
			

	Description
	-----------
	Audit all database logs file in MySqlServer.
		            
.LINK
    Author blog  : IdeasForFree  (http://blog.libinuko.com)
.LINK
    Author email : cakriwut@gmail.com

#>
    param (
	   $SQLServer
	)
   
    $errorMessage=@()
	$SMOserver = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
	
    $result = $SMOserver.Databases |? { !($_.IsSystemObject) } |% {    		    
			try {
              $logSize = ($_.logfiles | Measure-Object -Property Size -Sum).Sum/1KB
			  New-Object PSObject -Property @{
		              Name = $_.Name;
					  DBSize = $_.Size - $logSize;
					  LogSize = $logSize;
					  TotalSize = $_.Size;
					  DataSpaceUsage= $_.DataSpaceUsage;					  
					  IndexSpaceUsage = $_.IndexSpaceUsage;	
					  SpaceAvailable = $_.SpaceAvailable;
		       }
		    } catch {		      
			  $errorMessage += "Error acessing $($dbFile.Name) => $($dbFile.Status)"			  
		    }            
						
            } | select Name, DBSize, LogSize, TotalSize, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable
			
    $errorMessage
	return $result
}

function Compress-DatabaseLog
{

<#
.SYNOPSIS
Compress-DatabaseLog file on the server based to a specific threshold. 
		
.DESCRIPTION
Compress-DatabaseLog file on the server based on specified threshold. 
    
.INPUTS
None. You can not pipe objects to Shrink-DatabaseLog.
	
.OUTPUTS
Table of impacted database in form of @(Name,DBSize,LogSize,NewLogSize,TotalSize). 
		
.PARAMETER SQLServer
MANDATORY parameter to define target SQLServer 			

.PARAMETER Threshold
MANDATORY parameter to define maximum log filesize in MB.


.EXAMPLE
	PS>  Compress-DatabaseLog MySqlServer 300
			

	Description
	-----------
	Shrink all database logs file in MySqlServer if the log file size exceed 300 MB.
		            
.LINK
    Author blog  : IdeasForFree  (http://blog.libinuko.com)
.LINK
    Author email : cakriwut@gmail.com

#>
    param (
	   $SQLServer,
	   $Threshold = 50
	)
	
	$errorMessage=@()
	$SMOserver = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
	
    $result = $SMOserver.Databases |? { 
          $dbFile = $_
          try {
            !($_.IsSystemObject) -and ((($_.logfiles | Measure-Object -Property Size -Sum  -ErrorAction SilentlyContinue -ErrorVariable +err ).Sum/1KB) -gt 55 )
		  } catch {		      
			  $errorMessage += "Error acessing $($dbFile.Name) => $($dbFile.Status)"
			  $false
		  }} |% {     	    
            $logSize = ($_.logfiles | Measure-Object -Property Size -Sum).Sum/1KB		
		    $logs = $_.logfiles
			#easyway
		    $logs |% {			
		       $_.Shrink(50, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)
		       $logs.refresh($true)
            }
		
		    if(($logSizeNew = ($_.logfiles | Measure-Object -Property Size -Sum).Sum/1KB) -eq $logSize)		
		    {   #try the hardway
		   	    $backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
		        $backup.Action = "Log";
		        $backup.Database = $_.Name
		        $backup.LogTruncation = "TruncateOnly"
			    $backup.SqlBackup($SMOserver)
			    $logs |% {			
		           $_.Shrink(50, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)
		           $logs.refresh($true)
                }
			    $logSizeNew = ($_.logfiles | Measure-Object -Property Size -Sum).Sum/1KB
		    }
						
            New-Object PSObject -Property @{
		              Name = $_.Name;
					  DBSize = $_.Size - $logSize;
					  LogSize = $logSize;
					  NewLogSize = $logSizeNew;
					  TotalSize = $_.Size  
		     }} | select Name, DBSize, LogSize, NewLogSize, TotalSize | Format-Table -AutoSize -Wrap
			  
    $errorMessage
	return $result
}
